package com.export.service.dynamic;

import com.export.entity.ReportParam;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Component;

/**
 * 客户报表动态sql
 *
 * @author yuanqinglong
 * @since 2021/7/27 9:32
 */
@Component
public class CustomerReportDynamicStatement {


    //**********************************客户报表*************************************//


    /**
     * 客户报告导出sql
     *
     * @param reportParam 报告参数
     * @return {@link String}
     */
    public String customerReportExportSql(ReportParam reportParam) {
        String sql = "SELECT CONCAT('RSJ',LPAD(c.cust_id::text,4,'0')) AS cust_id,\n" +
                "MAX(c.cust_sname) AS cust_sname,\n" +
                "SUM(stat_req_num) AS stat_req_num,\n" +
                "SUM(stat_findout_num) AS stat_findout_num,\n" +
                "SUM(stat_fail_num) AS stat_fail_num\n" +
                "FROM sys.sys_stat sta\n" +
                "LEFT JOIN sys.sys_org o ON o.org_id = sta.stat_org_id\n" +
                "LEFT JOIN sys.sys_cust c ON c.cust_id = o.org_cust_id WHERE stat_date BETWEEN #{startDate} AND #{endDate}";
        sql += this.customerReportQueryConditions(reportParam);
        sql += " GROUP BY c.cust_id ";
        return sql;
    }




    /**
     * 客户报表查询条件
     *
     * @param reportParam 报告参数
     * @return {@link String}
     */
    public String customerReportQueryConditions(ReportParam reportParam) {
        String sql = "";
        if (StringUtils.isNotBlank(reportParam.getCustSname())) {
            sql += " AND cust_sname LIKE #{%custSname%}";
        }
        if (StringUtils.isNotBlank(reportParam.getCustId())) {
            sql += " AND CONCAT('RSJ',LPAD(cust_id::text,4,'0')) LIKE #{%custId%}";
        }
        return sql;
    }




    /**
     * 客户报告详情导出sql
     *
     * @param reportParam 报告参数
     * @return {@link String}
     */
    public String customerReportDetailExportSql(ReportParam reportParam) {
        String grouping = reportParam.statisticalTimeTypeGrouping();
        String sql = "SELECT\n" +
                "TO_CHAR(stat_date,'" + grouping + "') AS stat_date,\n" +
                " CONCAT('RSJ',LPAD(c.cust_id::text,4,'0')) AS cust_id ,cust_sname,\n" +
                "SUM(stat_req_num) AS stat_req_num,\n" +
                "SUM(stat_findout_num) AS stat_findout_num,\n" +
                "SUM(stat_fail_num) AS stat_fail_num\n" +
                "FROM sys.sys_stat sta\n" +
                "LEFT JOIN sys.sys_org o ON o.org_id = sta.stat_org_id\n" +
                "LEFT JOIN sys.sys_cust c ON c.cust_id = o.org_cust_id WHERE stat_date BETWEEN #{startDate} AND #{endDate} AND " +
                "CONCAT('RSJ',LPAD(c.cust_id::text,4,'0')) = #{custId} ";
        sql += " GROUP BY c.cust_id,TO_CHAR(stat_date,'" + grouping + "')";
        return sql;
    }



}
